﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using Worktool.CODE_Chris.SQL_DATA;

namespace Worktool.CODE_Chris.DataSourceHelpers
{
    public class SerialsHelper
    {
        private Serials parent;
        private GeneralHelper helper;
        private SQL sql;

        private DataGridView gridView1;

        private SqlDataAdapter sqlDataAdapter1 = new SqlDataAdapter();

        private BindingSource bindingSource1 = new BindingSource();

        private DataTable dataTable1;

        private String select1;
        private String update1;

        private Boolean isPaste = false;
        private int countPasteError = 0;

        private String sqlLogEntry = "";

        public SerialsHelper(DataGridView gridView1, Serials parent)
        {
            this.parent = parent;
            this.gridView1 = gridView1;
            init();
        }

        private void init()
        {
            sql = new SQL();
            helper = new GeneralHelper();

            populateSQLStrings();
        }

        #region Methoden für gridView1 (Hauptmaske)
        public void getData1()
        {
            try
            {
                gridView1.DataSource = bindingSource1;

                sqlDataAdapter1 = new SqlDataAdapter(select1, Login.connection);
                sqlDataAdapter1.UpdateCommand = new SqlCommand(update1, new SqlConnection(Login.connection));
                
                //*********UPDATE***************
                //Neu zu schreibende Parameter
                sqlDataAdapter1.UpdateCommand.Parameters.Add("@serialNumber", SqlDbType.NVarChar, 50, "Serial Number");
                sqlDataAdapter1.UpdateCommand.Parameters.Add("@serialDate", SqlDbType.Date, 10, "Serial Insert Date");
                sqlDataAdapter1.UpdateCommand.Parameters.Add("@serialUser", SqlDbType.NVarChar, 50, "serialUser");

                //Daten aus der Originalquelle
                SqlParameter parameter = sqlDataAdapter1.UpdateCommand.Parameters.Add("@Original_ID", SqlDbType.Int, 50, "ID");
                parameter.SourceVersion = DataRowVersion.Original;

                parameter = sqlDataAdapter1.UpdateCommand.Parameters.Add("@Original_Artikel_ID", SqlDbType.Int, 50, "Artikel_ID");
                parameter.SourceVersion = DataRowVersion.Original;
                parameter = sqlDataAdapter1.UpdateCommand.Parameters.Add("@Original_Manufacturer", SqlDbType.NVarChar, 255, "Manufacturer");
                parameter.SourceVersion = DataRowVersion.Original;
                parameter = sqlDataAdapter1.UpdateCommand.Parameters.Add("@Original_Model", SqlDbType.NVarChar, 255, "Model");
                parameter.SourceVersion = DataRowVersion.Original;
                parameter = sqlDataAdapter1.UpdateCommand.Parameters.Add("@Original_Description", SqlDbType.NVarChar, 255, "Description");
                parameter.SourceVersion = DataRowVersion.Original;

                parameter = sqlDataAdapter1.UpdateCommand.Parameters.Add("@Original_serialNumber", SqlDbType.NVarChar, 50, "Serial Number");
                parameter.SourceVersion = DataRowVersion.Original;
                parameter = sqlDataAdapter1.UpdateCommand.Parameters.Add("@Original_serialDate", SqlDbType.Date, 10, "Serial Insert Date");
                parameter.SourceVersion = DataRowVersion.Original;
                parameter = sqlDataAdapter1.UpdateCommand.Parameters.Add("@Original_serialUser", SqlDbType.NVarChar, 50, "serialUser");
                parameter.SourceVersion = DataRowVersion.Original;

                //Check auf Null-Werte
                parameter = sqlDataAdapter1.UpdateCommand.Parameters.Add("@IsNull_serialNumber", SqlDbType.Int, 50, "Serial Number");
                parameter.SourceVersion = DataRowVersion.Original;
                parameter.SourceColumnNullMapping = true;
                parameter = sqlDataAdapter1.UpdateCommand.Parameters.Add("@IsNull_Manufacturer", SqlDbType.Int, 255, "Manufacturer");
                parameter.SourceVersion = DataRowVersion.Original;
                parameter.SourceColumnNullMapping = true;
                parameter = sqlDataAdapter1.UpdateCommand.Parameters.Add("@IsNull_Model", SqlDbType.Int, 255, "Model");
                parameter.SourceVersion = DataRowVersion.Original;
                parameter.SourceColumnNullMapping = true;
                parameter = sqlDataAdapter1.UpdateCommand.Parameters.Add("@IsNull_Description", SqlDbType.Int, 255, "Description");
                parameter.SourceVersion = DataRowVersion.Original;
                parameter.SourceColumnNullMapping = true;
                parameter = sqlDataAdapter1.UpdateCommand.Parameters.Add("@IsNull_serialDate", SqlDbType.Int, 10, "Serial Insert Date");
                parameter.SourceVersion = DataRowVersion.Original;
                parameter.SourceColumnNullMapping = true;
                parameter = sqlDataAdapter1.UpdateCommand.Parameters.Add("@IsNull_serialUser", SqlDbType.Int, 50, "serialUser");
                parameter.SourceVersion = DataRowVersion.Original;
                parameter.SourceColumnNullMapping = true;

                SqlCommandBuilder commandBuilder = new SqlCommandBuilder(sqlDataAdapter1);

                // Populate a new data table and bind it to the BindingSource.
                dataTable1 = new DataTable();
                dataTable1.Locale = System.Globalization.CultureInfo.InvariantCulture;
                sqlDataAdapter1.Fill(dataTable1);
                bindingSource1.DataSource = dataTable1;

                gridView1.AutoGenerateColumns = false;

                //Zellennachbearbeitung
                setHiddenColumns1();
                setDisabledColumns1();

                //Eventhandler setzen
                gridView1.CellEndEdit -= new DataGridViewCellEventHandler(this.gridView1_CellEndEdit);
                gridView1.CellFormatting -= new DataGridViewCellFormattingEventHandler(this.gridView1_CellFormatting);
                gridView1.CellParsing -= new DataGridViewCellParsingEventHandler(this.gridView1_CellParsing);
                gridView1.CellMouseClick -= new DataGridViewCellMouseEventHandler(this.gridView1_CellMouseClick);
                gridView1.CellMouseDown -= new DataGridViewCellMouseEventHandler(this.gridView1_CellMouseDown);

                gridView1.CellFormatting += new DataGridViewCellFormattingEventHandler(this.gridView1_CellFormatting);
                gridView1.CellEndEdit += new DataGridViewCellEventHandler(this.gridView1_CellEndEdit);
                gridView1.CellParsing += new DataGridViewCellParsingEventHandler(this.gridView1_CellParsing);
                gridView1.CellMouseClick += new DataGridViewCellMouseEventHandler(this.gridView1_CellMouseClick);
                gridView1.CellMouseDown += new DataGridViewCellMouseEventHandler(this.gridView1_CellMouseDown);
            }
            catch (Exception e)
            {
                sql.log("FEHLER", e, this.ToString() + ":" + System.Reflection.MethodBase.GetCurrentMethod().Name);
            }
        }

        private void setDisabledColumns1()
        {
            try
            {
                gridView1.Columns["Manufacturer"].ReadOnly = true;
                gridView1.Columns["Model"].ReadOnly = true;
                gridView1.Columns["Description"].ReadOnly = true;
                gridView1.Columns["Serial Insert Date"].ReadOnly = true;
                gridView1.Columns["serialUser"].ReadOnly = true;
            }
            catch (Exception e)
            {

            }
        }

        private void setHiddenColumns1()
        {
            try
            {
                gridView1.Columns["ID"].Visible = false;
                gridView1.Columns["Artikel_ID"].Visible = false;
            }
            catch (Exception e)
            {

            }
        }

        private void gridView1_CellMouseDown(object sender, DataGridViewCellMouseEventArgs e)
        {
            if (e.Button == MouseButtons.Right)
            {
                if (e.RowIndex != -1 && e.ColumnIndex != -1)
                {
                    gridView1.CurrentCell = gridView1.Rows[e.RowIndex].Cells[e.ColumnIndex];
                }
                else
                {
                    gridView1.ClearSelection();
                }
            }
        }

        private void gridView1_CellParsing(object sender, DataGridViewCellParsingEventArgs e)
        {
            if (e.ColumnIndex == gridView1.Columns["Serial Number"].Index)
            {
                try
                {
                    gridView1.CurrentRow.Cells["Serial Insert Date"].Value = DateTime.Today;
                    gridView1.CurrentRow.Cells["serialUser"].Value = Login.user;

                    //Logeintrag vorbereiten; Wenn kein DBConcurrency, dann wird er später geschrieben
                    sqlLogEntry = "INSERT INTO tbl_log_vw_lbl_Serials (Artikel_ID, serialNumber, Manufacturer, Model, Description, serialDate, serialUser) VALUES("
                        + gridView1.CurrentRow.Cells["Artikel_ID"].Value.ToString() + ","
                        + "'" + e.Value.ToString() + "',"
                        + "'" + gridView1.CurrentRow.Cells["Manufacturer"].Value.ToString() + "',"
                        + "'" + gridView1.CurrentRow.Cells["Model"].Value.ToString() + "',"
                        + "'" + gridView1.CurrentRow.Cells["Description"].Value.ToString() + "',"
                        + "'" + Convert.ToDateTime(gridView1.CurrentRow.Cells["Serial Insert Date"].Value).ToShortDateString() + "',"
                        + "'" + gridView1.CurrentRow.Cells["serialUser"].Value.ToString() + "')";
                }
                catch (Exception ex)
                {
                }
            }

        }

        private void gridView1_CellEndEdit(object sender, DataGridViewCellEventArgs e)
        {
            //Hier werden die Daten der editierten Zelle wieder in die Datenbank geschrieben
            gridView1.BindingContext[gridView1.DataSource].EndCurrentEdit();
            updateGridView1();
        }

        public Boolean updateGridView1()
        {
            try
            {
                sqlDataAdapter1.Update(dataTable1);

                //Nur, wenn kein DBConcurrencyException:

                //Logeintrag schreiben
                sql.sendSQL(sqlLogEntry);

                //Label drucken
                parent.printLabel();

                return true;
            }
            catch (DBConcurrencyException e)
            {
                if (isPaste)
                {
                    countPasteError++;
                }
                else
                {
                    if (MessageBox.Show("Der Datensatz wurde in der Zwischenzeit auf dem Server bearbeitet. Die Datensätze werden neu geladen.", "Fehler", MessageBoxButtons.OK) == DialogResult.OK)
                    {
                        parent.reloadAllData();
                    }
                }

                sql.log("FEHLER", e, this.ToString() + ":" + System.Reflection.MethodBase.GetCurrentMethod().Name);
                return false;
            }
        }

        private void gridView1_CellFormatting(object sender, DataGridViewCellFormattingEventArgs e)
        {
            if (e.ColumnIndex != -1)
            {
                DataGridViewColumn col = gridView1.Columns[e.ColumnIndex];

                //Blaue Zellen
                if (col.Name == "Manufacturer" && col.DefaultCellStyle.BackColor != helper.getColors()[0]) col.DefaultCellStyle.BackColor = helper.getColors()[0];
                if (col.Name == "Model" && col.DefaultCellStyle.BackColor != helper.getColors()[0]) col.DefaultCellStyle.BackColor = helper.getColors()[0];
                if (col.Name == "Description" && col.DefaultCellStyle.BackColor != helper.getColors()[0]) col.DefaultCellStyle.BackColor = helper.getColors()[0];
                if (col.Name == "Serial Insert Date" && col.DefaultCellStyle.BackColor != helper.getColors()[0]) col.DefaultCellStyle.BackColor = helper.getColors()[0];
                if (col.Name == "serialUser" && col.DefaultCellStyle.BackColor != helper.getColors()[0]) col.DefaultCellStyle.BackColor = helper.getColors()[0];
            }
        }

        private void gridView1_CellMouseClick(object sender, DataGridViewCellMouseEventArgs e)
        {
            if (e.Button == MouseButtons.Right)
            {
                ContextMenu m = new ContextMenu();

                MenuItem copy = new MenuItem("Copy");
                copy.Click += new System.EventHandler(this.copy1_Click);
                m.MenuItems.Add(copy);

                MenuItem paste = new MenuItem("Paste");
                paste.Click += new System.EventHandler(this.paste1_Click);
                m.MenuItems.Add(paste);

                MenuItem delete = new MenuItem("Delete");
                delete.Click += new System.EventHandler(this.delete1_Click);
                m.MenuItems.Add(delete);

                var relativeMousePosition = gridView1.PointToClient(Cursor.Position);
                m.Show(gridView1, relativeMousePosition);
            }
        }

        private void copy1_Click(object sender, EventArgs e)
        {
            try
            {
                if (gridView1.SelectedCells.Count == 1 && gridView1.SelectedCells[0].Value.ToString() != "")
                {
                    Clipboard.SetText(gridView1.SelectedCells[0].Value.ToString());
                }
                else if (gridView1.SelectedCells.Count == 1)
                {
                    if (gridView1.SelectedCells[0].OwningColumn.CellType == typeof(DataGridViewComboBoxCell))
                    {
                        if (gridView1.SelectedCells[0].Value.ToString() != "")
                        {
                            Clipboard.SetText(gridView1.SelectedCells[0].Value.ToString());
                        }
                        else
                        {
                            Clipboard.Clear();
                        }
                    }
                    else
                    {
                        Clipboard.Clear();
                    }
                }
                else
                {
                    Clipboard.Clear();
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show("Copy to Clipboard war nicht erfolgreich. Bitte versuchen Sie es erneut.", "Fehler");
            }
        }

        private void paste1_Click(object sender, EventArgs e)
        {
            //Für Korrekte Behandlung der DBConcurrencyException
            isPaste = true;

            List<int> pasteZeilen = new List<int> { };
            string pasteColumn = "";
            if (gridView1.SelectedCells.Count != 0) pasteColumn = gridView1.SelectedCells[0].OwningColumn.Name;

            for (int i = 0; i < gridView1.SelectedCells.Count; i++)
            {
                pasteZeilen.Add(gridView1.SelectedCells[i].OwningRow.Index);
            }

            if (Clipboard.GetText() != "")
            {
                for (int i = 0; i < pasteZeilen.Count; i++)
                {
                    if (!gridView1.Rows[pasteZeilen[i]].Cells[pasteColumn].OwningColumn.ReadOnly)
                    {
                        try
                        {
                            if (pasteColumn == "bestellFreigabe" || pasteColumn == "Bestelltermin" || pasteColumn == "bestaetigterLiefertermin" || pasteColumn == "Wareneingang ASC" || pasteColumn == "Soll Bereitstellung HH" || pasteColumn == "Ist Bereitstellung HH" || pasteColumn == "Liefertermin an Werft" || pasteColumn == "Soll Bereitstellung Schiff")
                            {
                                if (!helper.IsDateExact(Clipboard.GetText()))
                                {
                                    MessageBox.Show("Der zu kopierende Text ist kein gültiges DateTime-Format. Keine Zeile wurde aktualisiert.", "Fehler");
                                    return;
                                }
                            }

                            gridView1.Rows[pasteZeilen[i]].Cells[pasteColumn].Value = Clipboard.GetText();

                            //Logeintrag schreiben, InsertDate + User ändern/neu schreiben
                            try
                            {
                                gridView1.Rows[pasteZeilen[i]].Cells["Serial Insert Date"].Value = DateTime.Today;
                                gridView1.Rows[pasteZeilen[i]].Cells["serialUser"].Value = Login.user;

                                //Logeintrag vorbereiten; Wenn kein DBConcurrency, dann wird er später geschrieben
                                sqlLogEntry = "INSERT INTO tbl_log_vw_lbl_Serials (Artikel_ID, serialNumber, Manufacturer, Model, Description, serialDate, serialUser) VALUES("
                                    + gridView1.Rows[pasteZeilen[i]].Cells["Artikel_ID"].Value.ToString() + ","
                                    + "'" + Clipboard.GetText() + "',"
                                    + "'" + gridView1.Rows[pasteZeilen[i]].Cells["Manufacturer"].Value.ToString() + "',"
                                    + "'" + gridView1.Rows[pasteZeilen[i]].Cells["Model"].Value.ToString() + "',"
                                    + "'" + gridView1.Rows[pasteZeilen[i]].Cells["Description"].Value.ToString() + "',"
                                    + "'" + Convert.ToDateTime(gridView1.Rows[pasteZeilen[i]].Cells["Serial Insert Date"].Value).ToShortDateString() + "',"
                                    + "'" + gridView1.Rows[pasteZeilen[i]].Cells["serialUser"].Value.ToString() + "')";
                            }
                            catch (Exception ex)
                            {
                            }

                            // Zeile zurück in die DB schreiben
                            gridView1_CellEndEdit(null, null);
                        }
                        catch (Exception ex)
                        {

                        }
                    }
                }
                if (countPasteError > 0)
                {
                    if (MessageBox.Show("Der Datensatz wurde in der Zwischenzeit auf dem Server bearbeitet. Es wurden nur die Datensätze aktualisiert, die nicht im Hintergrund bearbeitet wurden. Alle Datensätze werden nun neu geladen.", "Fehler", MessageBoxButtons.OK) == DialogResult.OK)
                    {
                        parent.reloadAllData();
                    }
                }
            }
            countPasteError = 0;
            isPaste = false;
        }

        private void delete1_Click(object sender, EventArgs e)
        {
            //Für Korrekte Behandlung der DBConcurrencyException
            isPaste = true;

            List<int> deleteZeilen = new List<int> { };
            string deleteColumn = "";
            if (gridView1.SelectedCells.Count != 0) deleteColumn = gridView1.SelectedCells[0].OwningColumn.Name;

            for (int i = 0; i < gridView1.SelectedCells.Count; i++)
            {
                deleteZeilen.Add(gridView1.SelectedCells[i].OwningRow.Index);
            }

            if (gridView1.SelectedCells.Count != 0)
            {
                for (int i = 0; i < gridView1.SelectedCells.Count; i++)
                {
                    if (!gridView1.SelectedCells[i].OwningColumn.ReadOnly)
                    {
                        try
                        {
                            if (gridView1.SelectedCells[i].ValueType == typeof(System.DateTime)) gridView1.SelectedCells[i].Value = DBNull.Value;
                            if (gridView1.SelectedCells[i].ValueType == typeof(int)) gridView1.SelectedCells[i].Value = 0;
                            if (gridView1.SelectedCells[i].ValueType == typeof(string)) gridView1.SelectedCells[i].Value = String.Empty;
                            if (gridView1.SelectedCells[i].ValueType == typeof(Boolean)) gridView1.SelectedCells[i].Value = Boolean.FalseString;

                            if (gridView1.SelectedCells[i].ValueType == typeof(System.DateTime) || gridView1.SelectedCells[i].ValueType == typeof(int) || gridView1.SelectedCells[i].ValueType == typeof(string) || gridView1.SelectedCells[i].ValueType == typeof(Boolean))
                            {
                                // Zeile zurück in die DB schreiben
                                gridView1_CellEndEdit(null, null);
                            }
                        }
                        catch (Exception ex)
                        {

                        }
                    }
                }

                if (countPasteError > 0)
                {
                    if (MessageBox.Show("Der Datensatz wurde in der Zwischenzeit auf dem Server bearbeitet. Es wurden nur die Datensätze aktualisiert, die nicht im Hintergrund bearbeitet wurden. Alle Datensätze werden nun neu geladen.", "Fehler", MessageBoxButtons.OK) == DialogResult.OK)
                    {
                        parent.reloadAllData();
                    }
                }
            }

            countPasteError = 0;
            isPaste = false;
        }

        public DataTable getTable1()
        {
            return dataTable1;
        }
        #endregion

        #region SQL-Befehle als Strings
        private void populateSQLStrings()
        {
            select1 = "SELECT ID, Artikel_ID, serialNumber AS [Serial Number], Manufacturer, Model, Description, serialDate AS [Serial Insert Date], serialUser FROM vw_lbl_Serials";
            update1 = "UPDATE vw_lbl_Serials SET serialNumber = @serialNumber, serialDate = @serialDate, serialUser = @serialUser "
                        +"WHERE (ID = @Original_ID) AND (Artikel_ID = @Original_Artikel_ID) AND (@IsNull_serialNumber = 1 AND serialNumber IS NULL OR "
                         +"serialNumber = @Original_serialNumber) AND (@IsNull_Manufacturer = 1 AND Manufacturer IS NULL OR "
                         +"Manufacturer = @Original_Manufacturer) AND (@IsNull_Model = 1 AND Model IS NULL OR "
                         +"Model = @Original_Model) AND (@IsNull_Description = 1 AND Description IS NULL OR "
                         +"Description = @Original_Description) AND (@IsNull_serialDate = 1 AND serialDate IS NULL OR "
                         +"serialDate = @Original_serialDate) AND (@IsNull_serialUser = 1 AND serialUser IS NULL OR "
                         +"serialUser = @Original_serialUser)";
        }
        #endregion
    }
}
